

*** Preparing data for 2000-2004
*** Preparing data for each month
foreach year in  "00" "01" "02" "03" "04"{
foreach month in "01" "02" "03" "04" "05" "06" "07" "08" "09" "10" "11" "12" {
use "${raw}XFEP`year'`month'.DTA", replace
keep if FEPA_PAISO=="G8" | FEPA_PAISO=="USA" /**Keeping excports to US**/
gen month="`month'"
gen year="20`year'"
keep year month FEPA_RFC FEPA_FRA FEPA_DOC FEPA_VOL FEPA_VAL
duplicates drop
save "${temp}exp_rfc_prod`year'`month'.dta", replace
}
}

*** Append monthly data
use "${temp}exp_rfc_prod0001.dta", replace
foreach month in "01" "02" "03" "04" "05" "06" "07" "08" "09" "10" "11" "12" {
append using "${temp}exp_rfc_prod00`month'.dta"
}

foreach year in "01" "02" "03" "04"{
foreach month in "01" "02" "03" "04" "05" "06" "07" "08" "09" "10" "11" "12" {
append using "${temp}exp_rfc_prod`year'`month'.dta"
}
}
 
rename FEPA_FRA frac
gen digit=length(frac)
replace frac=subinstr(frac, "Q","",.)
gen hs6=substr(frac,1,6)
gen hs2=substr(frac,1,2)
destring hs2, replace
keep if hs2>=50 & hs2<=63
rename FEPA_RFC rfc

collapse (sum) FEPA_VOL FEPA_VAL, by(year month frac rfc FEPA_DOC)
*** Construting Maquila indicator depending on the document type
gen definite=0
replace definite=1 if FEPA_DOC=="A1" 
replace definite=2 if FEPA_DOC=="RT" 
replace definite=1 if FEPA_DOC=="A1"
replace definite=1 if FEPA_DOC=="G1"
replace definite=1 if FEPA_DOC=="G6"
replace definite=1 if FEPA_DOC=="G7"
replace definite=1 if FEPA_DOC=="L1"
replace definite=1 if FEPA_DOC=="T1"
replace definite=1 if FEPA_DOC=="F8"
replace definite=1 if FEPA_DOC=="F9"

replace definite=2 if FEPA_DOC=="I1"
replace definite=2 if FEPA_DOC=="J1"
replace definite=2 if FEPA_DOC=="J2"
replace definite=2 if FEPA_DOC=="RT"

replace definite=4 if FEPA_DOC=="BA"
replace definite=4 if FEPA_DOC=="BE"
replace definite=4 if FEPA_DOC=="BM"
replace definite=4 if FEPA_DOC=="BO"
replace definite=4 if FEPA_DOC=="J3"
replace definite=4 if FEPA_DOC=="BP"
replace definite=4 if FEPA_DOC=="BR"
replace definite=4 if FEPA_DOC=="J4"

keep if definite!=0 /**dropping the observation where document type cannot be classified because of typo**/
gen maquila=1
replace maquila=0 if definite==1
gen hs6=substr(frac,1,6)
collapse (sum) FEPA_VAL FEPA_VOL, by(rfc hs6 maquila month year)
save "${output_data}exp_rfc_prod_textile20002004.dta", replace

*** Preparing data for 2004-2011

foreach year in "2004"{
foreach month in "06" "07" "08" "09" "10" "11" "12" { 
use "${temp}501_551_`month'`year'use", replace
/**Keeping exports to US***/
keep if cvepaisorigendestino=="USA"
/*dropping RFC that are used collectively by groups of people*/
drop if rfc=="EXTR920901TS4"
drop if rfc=="AADC930401KX7"
drop if rfc=="ESTU930401QZ7"
drop if rfc=="EJID930401SJ5"
drop if rfc=="EMB930401KH4"
drop if rfc=="OIN9304013N0"
drop if rfc=="EDM930614781"
drop if valorendolares==0

**Dropping individual and transportation companies
gen frfc=substr(rfc, 4, 1)
destring frfc, replace force
keep if frfc!=.
drop frfc _merge
rename rfc rfctransta
sort rfctransta
merge m:1 rfctransta using "${output_data}transportista_rfclist.dta"
keep if _merge==1
drop _merge
rename rfctransta rfc

/*** Distinguish between defenitive and temporal (non maquila and maquila) ***/
gen definite=0
replace definite=1 if cveddocument=="A1" 
replace definite=2 if cveddocument=="RT" 
replace definite=1 if cveddocument=="A1"
replace definite=1 if cveddocument=="G1"
replace definite=1 if cveddocument=="G6"
replace definite=1 if cveddocument=="G7"
replace definite=1 if cveddocument=="L1"
replace definite=1 if cveddocument=="T1"
replace definite=1 if cveddocument=="F8"
replace definite=1 if cveddocument=="F9"

replace definite=2 if cveddocument=="I1"
replace definite=2 if cveddocument=="J1"
replace definite=2 if cveddocument=="J2"
replace definite=2 if cveddocument=="RT"

replace definite=4 if cveddocument=="BA"
replace definite=4 if cveddocument=="BE"
replace definite=4 if cveddocument=="BM"
replace definite=4 if cveddocument=="BO"
replace definite=4 if cveddocument=="J3"
replace definite=4 if cveddocument=="BP"
replace definite=4 if cveddocument=="BR"
replace definite=4 if cveddocument=="J4"
drop if cveddocument=="T1" /**cannot be determined**/
drop if definite==0 /**dropping the observation where document type cannot be classified because of typo**/
gen dzero=(frac<10000000)
tostring frac, replace
replace frac="0"+frac if dzero==1
gen hs2=substr(frac, 1, 2)
gen hs4=substr(frac, 1, 4)
destring hs2, replace
/* create section*/ 
recode hs2 (1/5=1) (6/14=2) (15=3) (16/24=4) (25/27=5) (28/38=6) (39/40=7) (41/43=8) (44/46=9) (47/49=10) (50/63=11) (64/67=12) (68/70=13) (71=14) (72/83=15) (84/85=16) (86/89=17) (90/92=18) (93=19) (94/96=20) (97=21), gen(hs2section)
collapse (sum) valorendolares, by (rfc frac hs2 hs2section definite hs4)
gen month=`month'
gen year=`year'	
save ${temp}exp_all_firms_frac_rfc`month'`year', replace
}
}


foreach year in "2005" "2006" "2007" "2008" "2009" "2010" "2011"{
foreach month in "01" "02" "03" "04" "05" "06" "07" "08" "09" "10" "11" "12" { 
use "${temp}501_551_`month'`year'use", replace
keep if cvepaisorigendestino=="USA"	
/*dropping RFC that are used collectively by groups of people*/
drop if rfc=="EXTR920901TS4"
drop if rfc=="AADC930401KX7"
drop if rfc=="ESTU930401QZ7"
drop if rfc=="EJID930401SJ5"
drop if rfc=="EMB930401KH4"
drop if rfc=="OIN9304013N0"
drop if rfc=="EDM930614781"
drop if valorendolares==0
**Dropping individual and transportation companies
gen frfc=substr(rfc, 4, 1)
destring frfc, replace force
keep if frfc!=.
capture drop frfc _merge
rename rfc rfctransta
sort rfctransta
merge m:1 rfctransta using "${output_data}transportista_rfclist.dta"
keep if _merge==1
drop _merge
rename rfctransta rfc

gen definite=0
replace definite=1 if cveddocument=="A1" 
replace definite=2 if cveddocument=="RT" 
replace definite=1 if cveddocument=="A1"
replace definite=1 if cveddocument=="G1"
replace definite=1 if cveddocument=="G6"
replace definite=1 if cveddocument=="G7"
replace definite=1 if cveddocument=="L1"
replace definite=1 if cveddocument=="T1"
replace definite=1 if cveddocument=="F8"
replace definite=1 if cveddocument=="F9"

replace definite=2 if cveddocument=="I1"
replace definite=2 if cveddocument=="J1"
replace definite=2 if cveddocument=="J2"
replace definite=2 if cveddocument=="RT"

replace definite=4 if cveddocument=="BA"
replace definite=4 if cveddocument=="BE"
replace definite=4 if cveddocument=="BM"
replace definite=4 if cveddocument=="BO"
replace definite=4 if cveddocument=="J3"
replace definite=4 if cveddocument=="BP"
replace definite=4 if cveddocument=="BR"
replace definite=4 if cveddocument=="J4"
drop if cveddocument=="T1" /**cannot be determined**/
drop if definite==0 /**dropping the observation where document type cannot be classified because of typo**/
gen dzero=(frac<10000000)
tostring frac, replace
replace frac="0"+frac if dzero==1
gen hs2=substr(frac, 1, 2)
gen hs4=substr(frac, 1, 4)
destring hs2, replace
/* create section*/ 
recode hs2 (1/5=1) (6/14=2) (15=3) (16/24=4) (25/27=5) (28/38=6) (39/40=7) (41/43=8) (44/46=9) (47/49=10) (50/63=11) (64/67=12) (68/70=13) (71=14) (72/83=15) (84/85=16) (86/89=17) (90/92=18) (93=19) (94/96=20) (97=21), gen(hs2section)
collapse (sum) valorendolares, by (rfc frac hs2 hs2section definite hs4)
gen month=`month'
gen year=`year'	
save ${temp}exp_all_firms_frac_rfc`month'`year', replace
}
}


use "${temp}exp_all_firms_frac_rfc062004", replace
foreach year in "2004"{
foreach month in "08" "09" "10" "11" "12" {
append using "${temp}exp_all_firms_frac_rfc`month'`year'"

}
}

foreach year in "2005" "2006" "2007" "2008" "2009" "2010" "2011"{
foreach month in "01" "02" "03" "04" "05" "06" "07" "08" "09" "10" "11" "12" { 
append using "${temp}exp_all_firms_frac_rfc`month'`year'"
}
}
keep if hs2>=50 & hs2<=63
gen hs6=substr(frac,1,6)
gen maquila=1
replace maquila=0 if definite==1
collapse (sum) valor, by(rfc hs6 hs2 maquila month year)
save ${output_data}exp_rfc_prod_textile_us20042011.dta, replace








